package hk.ces.oxfam.patch.patchs;

import hk.ces.oxfam.patch.DBPatch;
import hk.ces.oxfam.patch.db.result.BeanResult;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.db.sql.SQLUtil;
import hk.ces.oxfam.patch.obj.BatchItem;
import hk.ces.oxfam.patch.obj.BeanPacker;
import hk.ces.oxfam.patch.xml.SQLResultXML;
import hk.ces.oxfam.patch.xml.factory.SQLResultXMLFactory;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
public class CheckDBPatch implements DBPatch
{
  private static final String BATCH_MATCHED = "MATCHED";
  private static final String BATCH_COMPLETED = "COMPLETED";
  private static final String ONLINE_PAYMENT = "ONLINE_PAYMENT";
  private static final String TXMETHOD_ONLINE = "ONLINE";
  private static final String TXSTATE_CONFIRM = "CONFIRM";
  private int batchCount = 0;

  public String getName()
  {
    return "Check Production DB patch";
  }

  public void patch(Connection connection) throws SQLException
  {
//    this.checkReceiptIsNullTransaction(connection);
//    this.issueLog106(connection);
//    this.checkTransactionCount(connection);
    this.checkB201101100001(connection);
//    SQLResultXMLFactory resultXML = new SQLResultXMLFactory("IssueLog107.xml");
//    String sql = "select count(*) as VERIFIED_PAYMENT_COUNT, LASTVERIFIEDDATE from paymentinfo where paymentmethod = ? and disabled = ? and verified = ? group by LASTVERIFIEDDATE";
//    Object[] params = new Object[]{"ONLINE_PAYMENT", false, true};
//    resultXML.setXMLItems(new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params))));
//    resultXML.writeToFile();
//
//    Calendar calendar = Calendar.getInstance();
//    calendar.setTime(new Date());
//    calendar.set(Calendar.DAY_OF_MONTH, 13);
//
//    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
//    sql = "select id, lastmodifieddate, invoiceno, LASTVERIFIEDBY, LASTVERIFIEDDATE from paymentinfo where  paymentmethod = ? and disabled = ? and verified = ? and LASTVERIFIEDDATE >= ? and LASTVERIFIEDDATE <= ?";
//    params = new Object[]{"ONLINE_PAYMENT", false, true, dateFormat.format(calendar.getTime()), dateFormat.format(calendar.getTime())};
//    resultXML.setXMLItems(new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params))));
//    resultXML.writeToFile();
//
//    this.auditLog(connection);

//    SQLResultXMLFactory factory = new SQLResultXMLFactory("IssueLog108.xml");
//    String sql = "select * from auditlog where disclower like ? and keywords = ?";
//    Object[] parasm = new Object[]{"%b201011010009%", "STATE_CHANGE"};
//    factory.setXMLItems(new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, parasm))));
//    factory.writeToFile();

//    SQLResultXMLFactory resultXML = new SQLResultXMLFactory("IssueLog106.xml");
//    SQLResultXML[] resultXMLs = new SQLResultXML[2];
//
//    String sql = "select * from paymentinfo where invoiceno = ?";
//    Object[] params = new Object[]{"TWOD201101090001"};
//    resultXMLs[0] = new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params)));
//
//    sql = "select * from banktransaction where id = ?";
//    params = new Object[]{10979};
//    resultXMLs[1] = new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params)));
//
//    resultXML.setXMLItems(resultXMLs);
//    resultXML.writeToFile();
  }

  private void checkReceiptIsNullTransaction(Connection connection) throws SQLException
  {
    String sql = "select id from transaction where receipt_id is null and disabled = ? and transactionstate <> ?";
    Object[] params = new Object[]{false, "REVISED"};

    SQLResultXMLFactory factory = new SQLResultXMLFactory("ReceiptIsNullTransaction.xml");
    factory.setXMLItems(new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params))));
    factory.writeToFile();
  }

  private void checkPaymentMatched(Connection connection) throws SQLException
  {
    String sql = "select * from paymentinfo where id in (select paymentinfos_id from batch_paymentinfo where batch_id in (select id from batch where batchno = ? and disabled = ?))";
    Object[] params = new Object[]{"B201008010001", false};

    SQLResultXMLFactory factory = new SQLResultXMLFactory("B201008010001.xml");
    factory.setXMLItems(new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params))));
    factory.writeToFile();
  }

  private void checkTransactionCount(Connection connection) throws SQLException
  {
    String sql = "select count(id) from personinfo where personinfotype is null";
    Object[] params = new Object[]{};
    SQLResult result1 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    sql = "select count(*) as person_count from person where id in (select person_id from person_personinfo where personinfolist_id in (select id from personinfo where personinfotype is null))";
    params = new Object[]{};
    SQLResult result2 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    sql = "select count(*) as transaction_count from transaction where disabled = ? and transactionstate = ?";
    params = new Object[]{false, "CONFIRM"};
    SQLResult result3 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    SQLResultXMLFactory resultXML = new SQLResultXMLFactory("checkTransactionCount.xml");
    resultXML.setXMLItems(new SQLResultXML(result1), new SQLResultXML(result2), new SQLResultXML(result3));
    resultXML.writeToFile();
  }

  private void checkB201101100001(Connection connection) throws SQLException
  {
    String sql = "select * from paymentinfo where id in (select paymentinfos_id from batch_paymentinfo where batch_id in (select id from batch where batchno = ?))";
//    String sql = "SELECT BATCH.BATCHNO, BATCH.DISABLED, PAYMENTINFO.ID, PAYMENTINFO.DISABLED, PAYMENTINFO.BANKACCOUNTNO, PAYMENTINFO.CHEQUENO, PAYMENTINFO.HKAMOUNT, PAYMENTINFO.INVOICENO, PAYMENTINFO.LASTVERIFIEDBY, PAYMENTINFO.LASTVERIFIEDDATE, PAYMENTINFO.PAYMENTMETHOD, PAYMENTINFO.PAYMENTTYPE, PAYMENTINFO.VERIFIED"
//            + " FROM (BATCH INNER JOIN BATCH_PAYMENTINFO ON BATCH.ID = BATCH_PAYMENTINFO.BATCH_ID) INNER JOIN PAYMENTINFO ON BATCH_PAYMENTINFO.PAYMENTINFOS_ID = PAYMENTINFO.ID"
//            + " WHERE (((BATCH.BATCHNO)='B201101100001'))";
    Object[] params = new Object[]{"B201101100001"};
    SQLResult result = SQLUtil.query(connection, new PreparedSQL(sql, params));

    SQLResultXMLFactory resultXML = new SQLResultXMLFactory("checkB201101100001.xml");
    resultXML.setXMLItems(new SQLResultXML(result));
    resultXML.writeToFile();
  }

  private void checkB201011010009(Connection connection) throws SQLException
  {
    String sql = "select * from transaction where id in (select trasnactions_id from batch_transaction where batch_id in (select id from batch where batchno = ?))";
    Object[] params = new Object[]{"B201011010009"};
    SQLResult result1 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    sql = "select * from receipt where id in (select receipt_id from transaction where id in (select trasnactions_id from batch_transaction where batch_id in (select id from batch where batchno = ?)))";
    params = new Object[]{"B201011010009"};
    SQLResult result2 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    sql = "select * from transactionrevision where id in (select transactionrevision_id from transactionrevision_transactionrevisionitem where revisionitems_id in (select id from transactionrevisionitem where type = ? and id in (select transactionrevisionitem_id from transactionrevisionitem_transaction where transactions_id in (select id from transaction where id in (select trasnactions_id from batch_transaction where batch_id in (select id from batch where batchno = ?))))))";
    params = new Object[]{"FROM", "B201011010009"};
    SQLResult result3 = SQLUtil.query(connection, new PreparedSQL(sql, params));


    SQLResultXMLFactory resultXML = new SQLResultXMLFactory("checkB201011010009.xml");
    resultXML.setXMLItems(new SQLResultXML(result1), new SQLResultXML(result2), new SQLResultXML(result3));
    resultXML.writeToFile();
  }

  private void auditLog(Connection connection) throws SQLException
  {
    SQLResultXMLFactory resultXML = new SQLResultXMLFactory("AuditLog.xml");
    String sql = "select * from auditlog order by createdDate desc fetch first 1000 rows only";
    Object[] params = new Object[]{};
    resultXML.setXMLItems(new SQLResultXML(SQLUtil.query(connection, new PreparedSQL(sql, params))));
    resultXML.writeToFile();
  }

  private void issueLog109(Connection connection) throws SQLException
  {
    List<BatchItem> batchItems = this.getAllOnlineBatch(connection).getPackedResult();
    int count = batchItems.size();
    Logger.getLogger(this.getClass()).debug("Total Online Batch[" + count + "]. Begin to Check");

    int index = 0;
    for (BatchItem batchItem : batchItems)
    {
      this.isAssignmentTeamNoMatchedOnlineDonationTeamNo(connection, batchItem, index, count);
      index++;
    }

    Logger.getLogger(this.getClass()).debug("Total Questioned Batch[" + batchCount + "]. End Check");
  }

  private void issueLog106(Connection connection) throws SQLException
  {
    SQLResultXMLFactory factory = new SQLResultXMLFactory("issueLog106");

    String sql = "select * from paymentinfo where invoiceno = ?";
    Object[] params = new Object[]{"TWOD201101090001"};
    SQLResult result1 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    sql = "select * from banktransaction where invoiceno = ?";
    params = new Object[]{"TWOD201101090001"};
    SQLResult result2 = SQLUtil.query(connection, new PreparedSQL(sql, params));

    factory.setXMLItems(new SQLResultXML(result1), new SQLResultXML(result2));
    factory.writeToFile();
  }

  private void isAssignmentTeamNoMatchedOnlineDonationTeamNo(Connection connection, BatchItem batchItem, int index, int count) throws SQLException
  {
    String sql1 = "select teamNo from otwteam where id in (select sponsoredteamid from transaction where id in (select trasnactions_id from batch_transaction where batch_id = ?) and method = ? and disabled = ? and transactionstate = ?)";
    Object[] params1 = new Object[]{batchItem.getId(), TXMETHOD_ONLINE, false, TXSTATE_CONFIRM};
    PreparedSQL preparedSQL1 = new PreparedSQL(sql1, params1);

    String sql2 = "select teamno from otwteamtotalamount where id in (select teamtotalamounts_id from batch_otwteamtotalamount where batch_id = ?)";
    Object[] params2 = new Object[]{batchItem.getId()};
    PreparedSQL preparedSQL2 = new PreparedSQL(sql2, params2);

    String fullSql = "(" + PreparedSQL.BASE_SQLS + ") UNION (" + PreparedSQL.BASE_SQLS + ")";
    Object[] fullParams = new Object[]{};
    PreparedSQL fullPreparedSQL = new PreparedSQL(fullSql, fullParams);
    fullPreparedSQL.setBaseSQLs(preparedSQL1, preparedSQL2);

    SQLResult result = SQLUtil.query(connection, fullPreparedSQL);

    if (result.getRowCount() == 1)
    {
//      Iterator<Object[]> iterator = result.getRows().iterator();
//      Logger.getLogger(this.getClass()).debug("[" + index + ":" + count + "]Same Team No. between Allocation and Assignment[" + iterator.next()[0] + "] in Batch[batchno=" + batchItem.getBatchNo() + "]");
    }
    else if (result.getRowCount() > 1)
    {
      Iterator<Object[]> iterator = result.getRows().iterator();
      Logger.getLogger(this.getClass()).error("[" + index + ":" + count + ":" + batchCount++ + "] -- Different Team No. between Allocation[" + iterator.next()[0] + "] and Assignment[" + iterator.next()[0] + "] in Batch[id=" + batchItem.getId() + ", batchno=" + batchItem.getBatchNo() + "]");
    }
    else
    {
//      Logger.getLogger(this.getClass()).warn("[" + index + ":" + count + "] -- Have got Team No in both Allocation and Assignment in Batch[id=" + batchItem.getId() + "batchno=" + batchItem.getBatchNo() + "]");
    }

//    if (allocationTeamNo != null && assignmentTeamNo != null && allocationTeamNo.equals(assignmentTeamNo))
//    {
//      Logger.getLogger(this.getClass()).debug("Same Team No. between Assignment and Allocation in Batch[batchno=" + batchItem.getBatchNo() + "]");
//    }
//    else
//    {
//      Logger.getLogger(this.getClass()).error("-- Different Team No. between Assignment[" + assignmentTeamNo + "] and Allocation[" + allocationTeamNo + "] in Batch[id=" + batchItem.getId() + "batchno=" + batchItem.getBatchNo() + "]");
//    }
  }

  private BeanResult<BatchItem> getAllOnlineBatch(Connection connection) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ? and verified = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String mainSql = "select id, batchNo from batch where disabled = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";
    Object[] objects = new Object[] {
      Boolean.FALSE,
      false,
      ONLINE_PAYMENT,
      true
    };

    BeanResult<BatchItem> beanResult = new BeanResult<BatchItem>(SQLUtil.query(connection, new PreparedSQL(mainSql, objects)))
    {
      @Override
      public BeanPacker<BatchItem> packer(final SQLResult result)
      {
        return new BeanPacker<BatchItem>()
        {
          public BatchItem pack(Object[] objects)
          {
            BatchItem item = new BatchItem();
            item.setId((Long) objects[0]);
            item.setBatchNo((String) objects[1]);

            return item;
          }
        };
      }
    };

    return beanResult;
  }
}
